package cc.messcat.util;

import cc.messcat.common.util.ExcelHandler;
import cc.messcat.common.util.StringUtil;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.lang.StringUtils;

import java.io.File;
import java.lang.reflect.Method;
import java.util.*;

public class PoorFamilyControllerExcelHandler extends ExcelHandler {

	/**
	 * 从Excel文件里读取数据保存到Map里
	 * 
	 * @param fileName
	 *            Excel文件的名称
	 * @return Map 数据文件，类型是Map<List<Object>>，每个map是一个sheet，
	 *         sheet包含list，list的泛型类型是一个Object，利用反射机制设置值
	 */
	// 户主关系
	static final List<String> relationList = Arrays.asList("户主","配偶","之子","之女","之儿媳","之女婿","之孙子","之孙女","之外孙子","之外孙女","之父","之母","之岳父","之岳母","之公公","之婆婆","之祖父","之祖母","之外祖父","之外祖母","其他");
	// 民族
	static final List<String> nationList = Arrays.asList("汉族","壮族","满族","回族","苗族","维吾尔族","土家族","彝族","蒙古族","藏族","布依族","侗族","瑶族","朝鲜族","白族","哈尼族","哈萨克族","黎族","傣族","畲族","傈僳族","仡佬族","东乡族","高山族","拉祜族","水族","佤族","纳西族","羌族","土族","仫佬族","锡伯族","柯尔克孜族","达斡尔族","景颇族","毛南族","撒拉族","布朗族","塔吉克族","阿昌族","普米族","鄂温克族","怒族","京族","基诺族","德昂族","保安族","俄罗斯族","裕固族","乌孜别克族","门巴族","鄂伦春族","独龙族","塔塔尔族","赫哲族","珞巴族","其他","其它");
	// 文化程度
	static final List<String> cultureList = Arrays.asList("文盲或半文盲","小学","初中","高中","大专及以上","学龄前儿童");
	// 在校生状况
	static final List<String> schoolList = Arrays.asList("非在校生","学前教育","小学","七年级","八年级","九年级","高中一年级","高中二年级","高中三年级","中职一年级","中职二年级","中职三年级","高职一年级","高职二年级","高职三年级","大专及以上");
	// 健康状况
	static final List<String> healthyList = Arrays.asList("健康","长期慢性病","患有大病","残疾");
	// 劳动技能
	static final List<String> labourList = Arrays.asList("普通劳动力","技能劳动力","丧失劳动力","无劳动力");
	//脱贫属性
	static final List<String> povertyList = Arrays.asList("返贫","未脱贫","已脱贫（不再享受政策）","已脱贫（享受政策）");
//  static final List<String> povertyList = Arrays.asList("返贫","未脱贫","已脱贫");
	//贫困户属性 
	static final List<String> attributeList = Arrays.asList("一般贫困户","低保户","五保户","低保贫困户","一般农户","五保贫困户");
	//主要致贫原因 
	static final  List<String> reasonList = Arrays.asList("因病","因残","因学","因灾","缺土地","缺水","缺技术","缺劳力","缺资金","交通条件落后","自身发展动力不足");
			
	
	// 储存 扶贫单位名称，行列
	static Map<String, String> areaExcMap = new HashMap<String, String>();
	// 可以为空的列
//	static List<String> notCellList = Arrays.asList("12", "13","15","16", "17");
	static List<String> notCellList = Arrays.asList("5","13", "14","16","17", "18","26","27");//添加了自然村字段对应的位置+1

	static StringBuffer errInfo = new StringBuffer();
	//储存 证件号码，行列
	static Map<String,String> equaMap = new HashMap<String,String>();

	@SuppressWarnings("unchecked")
	public static Map readExcel(String fileName, Map<String, Long> areaMap) throws Exception {
		Map data = new HashMap();
		String err = null;
		String packageName = "cc.messcat.util.";
		Workbook book = Workbook.getWorkbook(new File(fileName));
		String[] sheetNames = book.getSheetNames();
		// 判断是否为多表
		if (sheetNames!=null && sheetNames.length>1) {
			err = "excel文档不能存在多表";
			data.put("errInfo", err);
			return data;
		}
		
		int rows=0;
		for (int i = 0; i < sheetNames.length; i++) {
			// sheet的名称就是JavaBean的类名
			String sheetName = sheetNames[i];
			String className = packageName + "PoorFamilyExcelVo";

			// 获取sheet
			Sheet sheet = book.getSheet(sheetName);
			rows = sheet.getRows();
			// 判断是否为空
			if (rows == 0) {
				err = "excel文档为空";
				data.put("errInfo", err);
				return data;
			}
			// 判断行数
			if (rows > 200000) {
				err = "excel文档行数不能大于二十万";
				data.put("errInfo", err);
				return data;
			}

			// 表头的列序号与JavaBean属性名称的映射Map
			Map columnFieldName = new HashMap();

			// 循环行
			List dataList = new ArrayList();
			for (int rowIndex = 0; rowIndex < rows; rowIndex++) {

				// 获取行，一行包含N列，也就是Cell的数组
				Cell[] cell = sheet.getRow(rowIndex);
				//判断excel文档表头数量
			/*	if (cell!=null && cell.length <27) {
					err = "excel文档表头列数有误";
					data.put("errInfo", err);
					return data;
				}*/
				

				// 约定第一行是表头，也就是JavaBean的属性名称，保存进columnFieldName

				if (rowIndex == 2) {
					for (int cellIndex = 1; cellIndex < cell.length; cellIndex++) {
						if (cellIndex > 26 && StringUtils.isBlank( cell[cellIndex].getContents())) break;
						String temp;
						/*
						 * if(cell[cellIndex].getContents().equals("序号")){//
						 * temp = "serialNum"; }else
						 */
						// 贫困户表 PoorFamily
						if (cell[cellIndex].getContents().equals("县(市、区、旗)")) {
							temp = "county";
						} else if (cell[cellIndex].getContents().equals("乡(镇)")) {
							temp = "country";
						} else if (cell[cellIndex].getContents().equals("行政村")) {// 唯一
							temp = "village";
						} 
						else if (cell[cellIndex].getContents().equals("自然村")) {// 自然村--新增字段
							temp = "nameH";
						} 
						else if (cell[cellIndex].getContents().equals("户编号")) {
							temp = "numberH";
						}
						// 家庭成员表 Family
						else if (cell[cellIndex].getContents().equals("人编号")) {
							temp = "numberP";
						} else if (cell[cellIndex].getContents().equals("姓名")) {
							temp = "name";
						} else if (cell[cellIndex].getContents().equals("证件号码")) {
							temp = "idNumber";
						} else if (cell[cellIndex].getContents().equals("人数")) { // 未找到该字段
							temp = "peNumber";
						} else if (cell[cellIndex].getContents().equals("与户主关系")) {
							temp = "relation";
						} else if (cell[cellIndex].getContents().equals("民族")) {
							temp = "nation";
						} else if (cell[cellIndex].getContents().equals("文化程度")) {
							temp = "culture";
						} else if (cell[cellIndex].getContents().equals("在校生状况")) {
							temp = "school";
						} else if (cell[cellIndex].getContents().equals("健康状况")) {
							temp = "healthy";
						} else if (cell[cellIndex].getContents().equals("劳动技能")) {
							temp = "labour";
						} else if (cell[cellIndex].getContents().equals("务工状况")) {
							temp = "province";
						} else if (cell[cellIndex].getContents().equals("务工时间（月）")) {// 务工时间（月）
							temp = "workTime";
						} else if (cell[cellIndex].getContents().equals("参加大病医疗")) {
							temp = "large";
						}
						// 贫困户表 PoorFamily
						else if (cell[cellIndex].getContents().equals("脱贫属性")) {
							temp = "poverty";
						} else if (cell[cellIndex].getContents().equals("贫困户属性")) {
							temp = "attribute";
						} else if (cell[cellIndex].getContents().equals("主要致贫原因")) {
							temp = "reason";
						} else if (cell[cellIndex].getContents().equals("危房户")) {
							temp = "danger";
						} else if (cell[cellIndex].getContents().equals("饮水安全")) {
							temp = "security";
						} else if (cell[cellIndex].getContents().equals("饮水困难")) {
							temp = "difficulty";
						} else if (cell[cellIndex].getContents().equals("人均纯收入")) {
							temp = "perCapita";
						} else if (cell[cellIndex].getContents().equals("联系电话")) {
							temp = "contact";
						} else {
							temp = cell[cellIndex].getContents();
							err = "excel文档表头格式有误["+(rowIndex+1)+"行"+(char)('@'+(cellIndex+1))+"列]";
							data.put("errInfo", err);
							return data;
							
						}

						columnFieldName.put(cellIndex, temp);
					}
				}

				// 从第二行开始循环数据
				else if (rowIndex != 0 && rowIndex != 1) {

					// 类名前面已经含有包名，就可以使用class获取该JavaBean的实例
					Object javaBean = Class.forName(className).newInstance();

					// 循环每列数据并set进JavaBean的属性里
					for (int cellIndex = 1; cellIndex < cell.length; cellIndex++) {

						// 格子里的值
						String value = cell[cellIndex].getContents();
						
						if(cellIndex==14 && StringUtil.isNotBlank(value)){
							value = value.split(",")[value.split(",").length-1];
						}
						
						// 校验数据
						err = gainVerification(value, rowIndex + 1, cellIndex + 1,areaMap);

						// 按照列的序号获取表头的列名或JavaBean的属性名称
						String fieldName = (String) columnFieldName.get(cellIndex);

						// 拿到JavaBean里某属性的setter方法名称
						String setter = null;
						try {
							 setter = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
							
						} catch (Exception e) {
							if(StringUtil.isNotBlank(err)){
								data.put("errInfo", err);
								equaMap.clear();
								areaExcMap.clear();
								errInfo.setLength(0);
								book.close();
								return data;
							}
						}
						
						Method[] method0 = javaBean.getClass().getMethods();
						Class[] methodParaClass = null;

						// 循环找到setter里参数的类型
						for (int k = 0; k < method0.length; k++) {
							if (setter.equals(method0[k].getName())) {
								methodParaClass = method0[k].getParameterTypes();
								break;
							}
						}
						Method method = javaBean.getClass().getMethod(setter, methodParaClass);
						String methodParaClassName = methodParaClass[0].getName();

						// 如果参数类型是JavaBean类型，则设置JavaBean的id值即可
						if (getClassType(methodParaClassName) == 3) {
							if (StringUtil.isNotBlank(value) && !"null".equals(value)) {

								// 设置JavaBean的id值
								Object innerJavaBean = methodParaClass[0].newInstance();
								Method method1 = innerJavaBean.getClass().getMethod("setId", Long.class);
								method1.invoke(innerJavaBean, new Object[] { Long.parseLong(value) });

								// 再设置JavaBean对象到宿主JavaBean
								method.invoke(javaBean, new Object[] { innerJavaBean });
							}
						} else {

							// 从格子里拿到的值都是String，现在根据不同类型要转换为不同类型的值，并set进JavaBean里
							Object paraVal = getValueByClassType(methodParaClassName, value);
							method.invoke(javaBean, new Object[] { paraVal });
						}
					}

					// 一个JavaBean设置值完成后则保存进list里

					dataList.add(javaBean);
				}
			}

			// 一个sheet循环完后则保存进data里
			data.put(sheetName, dataList);
		}
		
		data.put("errInfo", err);
		errInfo.delete(0, errInfo.length());
		equaMap.clear();
		areaExcMap.clear();
		book.close();
		return data;
	}

	/**
	 * 校验方法
	 * 
	 * @param value
	 *            值
	 * @param rowIndex
	 *            行
	 * @param cellIndex
	 *            列
	 * @param areaMap 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private static String gainVerification(String value, int rowIndex, int cellIndex, Map<String, Long> areaMap) {
		// 为空判断
		if (!notCellList.contains(String.valueOf(cellIndex))) {
			if (StringUtil.isBlank(value)) {
				rowIndexCell(rowIndex, cellIndex).append("必输项为空<br/>");
			}
		}
		
		if((cellIndex == 3 ||cellIndex == 4) && StringUtil.isNotBlank(value)){
			
			String rcInfo = rowIndex + "行" + (char) ('@' + cellIndex) + "列";
			// 储存(4行B列=魏县国家税务局)
			areaExcMap.put(rcInfo, value);
			if (cellIndex == 4) {
				// 获取B列(4行C列=魏城镇)
				String bCell = String.valueOf(areaExcMap.get(rcInfo.replace("D", "C")));
				// 获取C列(4行D列=北罗营村委会)
				String cCell = String.valueOf(areaExcMap.get(rcInfo));
				// 重新拼接需要储存的格式 格式：K：乡镇_村 V： 4行D列
				String newInfo = bCell + "_" + cCell;
				// 判断乡镇和村在数据库有没相同数据
				if (!areaMap.containsKey(newInfo)) {
					rowIndexCell(rowIndex, cellIndex).append("乡(镇)和行政村与数据库信息不一致<br/>");
				} 
				// 清除数据
				areaExcMap.remove(rcInfo.replace("D", "C"));
				areaExcMap.remove(rcInfo);
			}
		}
		
		
		// 户主关系
		if (cellIndex == 10+1) {
			if (StringUtil.isNotBlank(value) &&!relationList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		// 民族
		if (cellIndex == 11+1) {
			if (StringUtil.isNotBlank(value) &&!nationList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		// 文化程度 
		if (cellIndex == 12+1) {
			if (StringUtil.isNotBlank(value)&&!cultureList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		// 在校生状况
		if (cellIndex == 13+1) {
			if (StringUtil.isNotBlank(value)&&!schoolList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		// 健康状况
		if (cellIndex == 14+1) {
			if (StringUtil.isNotBlank(value) &&!healthyList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		// 劳动技能
		if (cellIndex == 15+1) {
			if (StringUtil.isNotBlank(value)&&!labourList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		
		
		//脱贫属性
		if (cellIndex == 19+1) {
			if (StringUtil.isNotBlank(value)&&!povertyList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		
		//贫困户属性 
		if (cellIndex == 20+1) {
			if (StringUtil.isNotBlank(value)&&!attributeList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		
		//主要致贫原因 
		if (cellIndex == 21+1) {
			if (StringUtil.isNotBlank(value)&&!reasonList.contains(value)) {
				rowIndexCell(rowIndex, cellIndex).append("录入的信息与提供的字典信息不一致<br/>");
			}
		}
		
//			导入的证件号码列中，相同号码出现超过一次的
//		if (cellIndex == 8+1 && StringUtil.isNotBlank(value)) {
//			if (equaMap.containsKey(value)) {
//				rowIndexCell(rowIndex, cellIndex).append(equaMap.get(value)+"证件号码相同<br/>");
//			}else{
//				//储存 证件号码，行列
//				equaMap.put(value , rowIndex+"行"+(char)('@'+cellIndex)+"列");
//			}
//			
//		}
		
		return errInfo.toString();
	}

	public static StringBuffer rowIndexCell(int rowIndex, int cellIndex) {
		return errInfo.append(rowIndex).append("行").append((char)('@'+cellIndex)).append("列，");
	}
 
	 
	 
	 

 

}
